﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Google.Protobuf.WellKnownTypes;
using MySql.Data.MySqlClient;

namespace GeodeticCoordinates
{
    internal class operateDatabase
    {
        public MySqlConnectionStringBuilder Builder = new MySqlConnectionStringBuilder();
        public MySqlConnection Connection;
        private MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
        private MySqlConnection connection;
        public string sql
        {
            set { _sql = value; }
            get { return _sql; }
        }
        private string _sql;

        /// <summary>
        /// 连接数据库的必要操作，包括了电脑的id 密码 服务器名称以及要操作的数据库名称
        /// </summary>
        public void GetData()
        {
            builder.UserID = "root";
            builder.Password = "123456";
            builder.Server = "localhost";
            builder.Database = "exercitation";
        }
        public MySqlConnectionStringBuilder GetData(int n)
        {
            Builder.UserID = "root";
            Builder.Password = "123456";
            Builder.Server = "localhost";
            Builder.Database = "exercitation";

            return Builder;
        }
        public string mysqltableName = "myfirstDemo";

        /// <summary>
        /// 创建数据库新表
        /// </summary>
        /// <returns>bool类型 判断是否创建成功</returns>
        public bool CreatedMysqlTable()
        {

            GetData();
            #region 创建mysql原始数据库表
            using (connection = new MySqlConnection(builder.ConnectionString))
            {
                connection.Open();

                string createStatement = "CREATE TABLE " + mysqltableName + " (pointName VarChar(10) not null primary key comment '点号',latitude（dms） Varchar(99) default '0' comment'纬度（dms)'," +
                    "lontitude（dms） Varchar(99) default '0' comment '经度（dms）',gaosi_x Varchar(99) default '0' comment '高斯平面x坐标',gaosi_y Varchar(99) default '0' comment '高斯平面y坐标')";
                using (MySqlCommand cmd = new MySqlCommand(createStatement, connection))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();
                        InsertTxt();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                        return false;
                    }
                }
                #endregion
            }
        }

        /// <summary>
        /// 删除mysql数据库表
        /// </summary>
        /// <returns>bool类型，判断是否删除成功</returns>
        public bool DeleteMysqlTable()
        {
            GetData();
            #region 删除mysql数据库表
            using (connection = new MySqlConnection(builder.ConnectionString))
            {
                connection.Open();

                string createStatement = "DROP TABLE " + mysqltableName;
                using (MySqlCommand cmd = new MySqlCommand(createStatement, connection))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                        return false;
                    }
                }
                #endregion
            }
        }

        /// <summary>
        /// 将txt文件导入至数据库
        /// </summary>
        public void InsertTxt()
        {
            operateFiles operateFiles = new operateFiles();
            string[][] str = operateFiles.AddFiles();

            GetData();
            for (int i = 0; i < str.Length; i++)
            {
                if (str[i] == null)
                {
                    break;
                }
                #region 插入数据到mysql数据库表
                connection = new MySqlConnection(builder.ConnectionString);
                try
                {
                    connection.Open();
                    string sql = "insert into " + mysqltableName + "(pointName,latitude（dms）,lontitude（dms）) values ('" + str[i][0] + "','" + str[i][1] + "','" + str[i][2] + "'" + ");";

                    MySqlCommand cmd = new MySqlCommand(sql, connection);
                    cmd.ExecuteNonQuery();
                    connection.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
                #endregion
            }
            MessageBox.Show("添加成功！");
        }

        public void ExportData()
        {

        }

        /// <summary>
        /// 删除选中的数据
        /// </summary>
        public void DeleteDate()
        {
            GetData();
            connection = new MySqlConnection(builder.ConnectionString);

            try
            {
                connection.Open();

                MySqlCommand cmd = new MySqlCommand(_sql, connection);
                cmd.ExecuteNonQuery();
                MessageBox.Show("删除成功！");
                connection.Close();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        public void UpdateDate()
        {
            GetData();
            connection = new MySqlConnection(builder.ConnectionString);

            try
            {
                connection.Open();

                MySqlCommand cmd = new MySqlCommand(_sql, connection);
                cmd.ExecuteNonQuery();
                MessageBox.Show("修改成功！");
                connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }
    }
}
